package com.bysj.money_diary;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class Data {
	//数据库名称
	public static final String DATABASE_NAME = "data.db";
	//数据库版本
	public static final int    VERSION_CODE = 1;
	private static final String TAG = null;
	private SQLiteDatabase db;
	private DatabaseHelper dbHelper;
	private Context context;
	
	private String U_NAME = "U_NAME";
	private String U_PASSWORD = "U_PASSWORD";
	private String U_PHO = "U_PHO";
	private String U_ANS = "U_BEIZHU";
	
	public Data(Context context){
		this.context = context;
        dbHelper=new DatabaseHelper(context,DATABASE_NAME,null,VERSION_CODE);
        db= dbHelper.getWritableDatabase();
	}
	public void close(){
        db.close();
        dbHelper.close();
    }
	
	/*
     *获取用户表中的所有数据
     * */
	public ArrayList<User>GetUserList(String selection){
		ArrayList<User> userlist=new ArrayList<User>();
		Cursor c=db.query(DatabaseHelper.USER, null, selection, null, null, null, "ID DESC");//降序
		c.moveToFirst();
		while(!c.isAfterLast()&&(c.getString(1)!=null)){
			User user=new User();
			user.setU_Id(c.getInt(0));
			user.setU_Name(c.getString(1));
			user.setU_Password(c.getString(2));
			user.setU_Pho(c.getString(3));
			user.setU_Pic(c.getString(4));
			user.setU_Beizhu(c.getString(5));
			userlist.add(user);
			c.moveToNext();
		}
		c.close();
		
		return userlist;		
	}
	
	/*
     *获取支出表中所有数据
     * */
	public ArrayList<Zhichu> GetZhiChuList(String selection){
        ArrayList<Zhichu> zhichulist=new ArrayList<Zhichu>();
    	Cursor cursor=db.query(DatabaseHelper.ZHICHU, null, selection, null, null, null, "ID DESC");
	    	cursor.moveToFirst();
	    	while(!cursor.isAfterLast()&&(cursor.getString(1)!=null)){
	    		Zhichu zhichu=new Zhichu();
	    		zhichu.setZc_Id(cursor.getInt(0));
	    		zhichu.setZc_Item(cursor.getString(1));
	    		zhichu.setZc_SubItem(cursor.getString(2));
	    		zhichu.setZc_Year(cursor.getInt(3));
	    		zhichu.setZc_Month(cursor.getInt(4));
	    		zhichu.setZc_Week(cursor.getInt(5));
	    		zhichu.setZc_Day(cursor.getInt(6));
	    		zhichu.setZc_Time(cursor.getString(7));
	    		zhichu.setZc_Pic(cursor.getString(8));
	    		zhichu.setZc_Count(cursor.getDouble(9));
	    		zhichu.setZc_Beizhu(cursor.getString(10));
	    		zhichulist.add(zhichu);
	    		cursor.moveToNext();
    	}
    	cursor.close();
    	return zhichulist;
    }
    
    /*
     * 获取收入表中所有数据
     * */
    public ArrayList<Shouru> GetShouRuList(String selection){
        ArrayList<Shouru> shourulist=new ArrayList<Shouru>();
    	Cursor cursor=db.query(DatabaseHelper.SHOURU, null, selection, null, null, null, "ID DESC");
	    	cursor.moveToFirst();
	    	while(!cursor.isAfterLast()&&(cursor.getString(1)!=null)){
	    		//isAfterLast():是否到最后
	    		Shouru shouru=new Shouru();
	    		shouru.setSr_Id(cursor.getInt(0));
	    		shouru.setSr_Item(cursor.getString(1));
	    		shouru.setSr_Year(cursor.getInt(2));
	    		shouru.setSr_Month(cursor.getInt(3));
	    		shouru.setSr_Week(cursor.getInt(4));
	    		shouru.setSr_Day(cursor.getInt(5));
	    		shouru.setSr_Time(cursor.getString(6));
	    		shouru.setSr_Count(cursor.getDouble(7));
	    		shouru.setSr_Beizhu(cursor.getString(8));
	    		shourulist.add(shouru);
	    		cursor.moveToNext();
    	}
    	cursor.close();
    	return shourulist;
    }
    
    /*
     * 找回密码
     * */
    public int UpdateUserInfo(User user,String paw,String name){
    	ContentValues v = new ContentValues();//ContentValues类似于一种存储机构，只能存储基础类型数据
    	v.put(User.U_PASSWORD, paw);
    	int idupdate= db.update(DatabaseHelper.USER,v,"U_NAME=?", new String[]{name});
    	Log.i(TAG,"UpdateUserInfo , idupdate="+idupdate); 
    	return idupdate;
    }
    /*
     * 更新支出表
     * */
    public int UpdateZhiChuInfo(Zhichu zhichu,int id){
        ContentValues values = new ContentValues();
        values.put(Zhichu.ZC_ITEM, zhichu.getZc_Item());
        values.put(Zhichu.ZC_SUBITEM, zhichu.getZc_SubItem());
        values.put(Zhichu.ZC_YEAR, zhichu.getZc_Year());
        values.put(Zhichu.ZC_MONTH, zhichu.getZc_Month());
        values.put(Zhichu.ZC_WEEK, zhichu.getZc_Week());
        values.put(Zhichu.ZC_DAY, zhichu.getZc_Day());
        values.put(Zhichu.ZC_TIME, zhichu.getZc_Time());
        values.put(Zhichu.ZC_PIC, zhichu.getZc_Pic());
        values.put(Zhichu.ZC_COUNT, zhichu.getZc_Count());
        values.put(Zhichu.ZC_BEIZHU, zhichu.getZc_Beizhu());
        int idupdate= db.update(DatabaseHelper.ZHICHU, values, "ID ='"+id+"'", null);
        this.close();
        return idupdate;
    }
    /*
     * 更新收入表
     * */
    public int UpdateShouRuInfo(Shouru shouru,int id){
        ContentValues values = new ContentValues();
        values.put(Shouru.SR_ITEM, shouru.getSr_Item());
        values.put(Shouru.SR_YEAR, shouru.getSr_Year());
        values.put(Shouru.SR_MONTH, shouru.getSr_Month());
        values.put(Shouru.SR_WEEK, shouru.getSr_Week());
        values.put(Shouru.SR_DAY, shouru.getSr_Day());
        values.put(Shouru.SR_TIME, shouru.getSr_Time());
        values.put(Shouru.SR_COUNT, shouru.getSr_Count());
        values.put(Shouru.SR_BEIZHU, shouru.getSr_Beizhu());
        int idupdate= db.update(DatabaseHelper.SHOURU, values, "ID ='"+id+"'", null);
        this.close();
        return idupdate;
    }
    
    /*
     * 添加用户表记录
     * */
    public Long SaveUserInfo(User user){
    	ContentValues v= new ContentValues();
    	v.put(User.U_NAME, user.getU_Name());
    	v.put(User.U_PASSWORD,user.getU_Password());
    	v.put(User.U_PHO, user.getU_Pho());
    	v.put(User.U_PIC,user.getU_Pic());
    	v.put(User.U_BEIZHU, user.getU_Beizhu());
    	Long uid= db.insert(DatabaseHelper.USER, User.U_NAME, v);   	
    	return uid;
    }
    public Long CloseUser(User user){
    	this.close();
		return null;   	
    }
    /*
     * 添加支出表记录
     * */
    public Long SaveZhiChuInfo(Zhichu zhichu){
        ContentValues values = new ContentValues();
        values.put(Zhichu.ZC_ITEM, zhichu.getZc_Item());
        values.put(Zhichu.ZC_SUBITEM, zhichu.getZc_SubItem());
        values.put(Zhichu.ZC_YEAR, zhichu.getZc_Year());
        values.put(Zhichu.ZC_MONTH, zhichu.getZc_Month());
        values.put(Zhichu.ZC_WEEK, zhichu.getZc_Week());
        values.put(Zhichu.ZC_DAY, zhichu.getZc_Day());
        values.put(Zhichu.ZC_TIME, zhichu.getZc_Time());
        values.put(Zhichu.ZC_PIC, zhichu.getZc_Pic());
        values.put(Zhichu.ZC_COUNT, zhichu.getZc_Count());
        values.put(Zhichu.ZC_BEIZHU, zhichu.getZc_Beizhu());
        Long uid = db.insert(DatabaseHelper.ZHICHU, Zhichu.ZC_YEAR, values);
        this.close();
        return uid;
    }
    
    /*
     * 添加收入表记录
     * */
    public Long SaveShouRuInfo(Shouru shouru){
        ContentValues values = new ContentValues();
        values.put(Shouru.SR_ITEM, shouru.getSr_Item());
        values.put(Shouru.SR_YEAR, shouru.getSr_Year());
        values.put(Shouru.SR_MONTH, shouru.getSr_Month());
        values.put(Shouru.SR_WEEK, shouru.getSr_Week());
        values.put(Shouru.SR_DAY, shouru.getSr_Day());
        values.put(Shouru.SR_TIME, shouru.getSr_Time());
        values.put(Shouru.SR_COUNT, shouru.getSr_Count()); 
        values.put(Shouru.SR_BEIZHU, shouru.getSr_Beizhu()); 
        Long uid = db.insert(DatabaseHelper.SHOURU, Shouru.SR_YEAR, values);
        this.close();
        return uid;
    }
    
    /*
     * 用户表删除
     * */
    public int DelUserInfo(int id){
    	int iddel= db.delete(DatabaseHelper.USER, "ID ="+id, null);
    	this.close();
    	return iddel;
    }
    /*
     * 支出表删除
     * */
    public int DelZhiChuInfo(int id){
        int iddel=  db.delete(DatabaseHelper.ZHICHU, "ID ="+id, null);
        this.close();
        return iddel;
    }
    
    /*
     * 两个表都删除
     * */
    public void delAll(){
    	DatabaseHelper.saveYuSuan(context,DatabaseHelper.YUSUAN_MONTH,DatabaseHelper.YUSUAN_MONTH, 0);
    	db.delete(DatabaseHelper.ZHICHU, null, null);
    	db.delete(DatabaseHelper.SHOURU, null, null);
    }
    /*
     * 判断某条是否存在
     */
    public boolean haveZhiChuInfo(int id){
    	boolean flag=false;
    	Cursor cursor=db.query(DatabaseHelper.ZHICHU, null,"ID ='"+id+"'", null, null, null, null);
    	flag=cursor.moveToFirst();
    	cursor.close();
    	return flag;
    }
    /*
     * 收入表删除
     * */
    public int DelShouRuInfo(int id){
        int iddel=  db.delete(DatabaseHelper.SHOURU, "ID ="+id, null);
        this.close();
        return iddel;
    }
    /*
     * 判断用户名是否存在
     */
    public boolean haveUserInfo(String name){
    	boolean flag=false;
    	Cursor cursor=db.query(DatabaseHelper.USER, null,"U_NAME ='"+name+"'", null, null, null, null);
    	flag=cursor.moveToFirst();
    	cursor.close();
    	return flag;
    }
    /*
     * 登录时用到，判断输入账号密码是否注册
     */
    public int UserbyNameAndPwd(String Name,String pwd){    
    	Log.i(TAG,"findUserByNameAndPho");
        int result=0;  
        Cursor mCursor=db.query(DatabaseHelper.USER, null, U_NAME+ "=\"" + Name + "\""+" and "+U_PASSWORD+ "=\"" + pwd + "\"",  
                null, null, null, null);  
        if(mCursor!=null){  
            result=mCursor.getCount();    
            Log.i(TAG,"findUserByNameAndPho , result="+result); 
        }  
        return result;  
    }
    /*
     * 登录时用到，判断输入账号密码是否注册
     */
    public String UserbyShualian(String Name){    
        String result = null;  
        Cursor mCursor=db.query(DatabaseHelper.USER, null, U_NAME+ "=\"" + Name + "\"",  
                null, null, null, null);
        Log.i(TAG,"UserbyShualian , mCursor="+mCursor);
        if(mCursor!=null){  
        	if(mCursor.moveToFirst()){
				while(!mCursor.isAfterLast()){
					result=mCursor.getString(5);  
				}				
        	}  
        }  
        return result;  
    }
    /*
     * 根据账号，手机号和注册时填写的问题答案判断是否正确
     */
    
    public int findUserByNameAndPhoAndAns(String userName, String userPho,
			String userAns) {
    	int result=0;  
    	Cursor mCursor=db.query(DatabaseHelper.USER, null, U_NAME+ "=\"" + userName + "\""+" and "+U_PHO+ "=\"" + userPho + "\""+" and "+U_ANS+ "=\"" + userAns + "\"",  
                null, null, null, null);  
        if(mCursor!=null){  
            result=mCursor.getCount();    
            Log.i(TAG,"findUserByNameAndPho , result="+result); 
        }   
		return result;
	}
	
    /*
     * 通过年月日查找某天的金额
     */    
    public int findZhichuByYearAndMonthAndDay(int flag, int year, int month,
			int day) {
    	day = day + 1;
    	int result=0;  
    	if(flag==0){
    		Cursor mCursor=db.query(DatabaseHelper.ZHICHU, null, "ZC_YEAR"+ "=\"" + year + "\""+" and "+"ZC_MONTH"+ "=\"" + month + "\""+" and "+"ZC_DAY"+ "=\"" + day + "\"",  
                    null, null, null, null);  
    		if(mCursor!=null){  
    			int temp = 0;
    			if(mCursor.moveToFirst()){
    				while(!mCursor.isAfterLast()){
	                	temp = temp + mCursor.getInt(mCursor.getColumnIndex("ZC_COUNT"));               	
	                	mCursor.moveToNext();  
	                }
    			}
                result = temp;
            }else
            	result = 0;
    	}else{
    		Cursor mCursor=db.query(DatabaseHelper.SHOURU, null, "SR_YEAR"+ "=\"" + year + "\""+" and "+"SR_MONTH"+ "=\"" + month + "\""+" and "+"SR_DAY"+ "=\"" + day + "\"",  
                    null, null, null, null);  	
    		if(mCursor!=null){  
    			int temp = 0;
    			if(mCursor.moveToFirst()){
    				while(!mCursor.isAfterLast()){
	                	temp = temp + mCursor.getInt(mCursor.getColumnIndex("SR_COUNT"));               	
	                	mCursor.moveToNext();  
	                }
    			}
                result = temp;
            }else
            	result = 0;
    	} 
		return result;
	}
    
    /*
     * 通过年月查找Item的金额
     */    
    public int findCountByYearAndMonth(int flag, int year, int month,
			String item) {
    	int result=0;  
    	if(flag==0){
    		Cursor mCursor=db.query(DatabaseHelper.ZHICHU, null, "ZC_YEAR"+ "=\"" + year + "\""+" and "+"ZC_MONTH"+ "=\"" + month + "\""+" and "+"ZC_ITEM"+ "=\"" + item + "\"",  
                    null, null, null, null);  
    		if(mCursor!=null){  
    			int temp = 0;
    			if(mCursor.moveToFirst()){
    				while(!mCursor.isAfterLast()){
	                	temp = temp + mCursor.getInt(mCursor.getColumnIndex("ZC_COUNT"));               	
	                	mCursor.moveToNext();  
	                }
    			}
                result = temp;
            }else
            	result = 0;
    	}else{
    		Cursor mCursor=db.query(DatabaseHelper.SHOURU, null, "SR_YEAR"+ "=\"" + year + "\""+" and "+"SR_MONTH"+ "=\"" + month + "\""+" and "+"SR_ITEM"+ "=\"" + item + "\"",  
                    null, null, null, null);  	
    		if(mCursor!=null){  
    			int temp = 0;
    			if(mCursor.moveToFirst()){
    				while(!mCursor.isAfterLast()){
	                	temp = temp + mCursor.getInt(mCursor.getColumnIndex("SR_COUNT"));               	
	                	mCursor.moveToNext();  
	                }
    			}
                result = temp;
            }else
            	result = 0;
    	} 
		return result;
	}
	
}
